package edu.jit.flowers.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import edu.jit.flowers.bean.Flowers;
import edu.jit.flowers.utils.JDBCUtil;

public class FlowersDAO {
	//添加花
    public int addFlowers(Flowers f) {
   	 int rows=0;
   	 String sql="insert into flowers values(0,?,?,?,?,?,?)";
   	 
   	 QueryRunner runner=new QueryRunner(JDBCUtil.getDataSource());
   	 try {
			rows=runner.update(sql, f.getfName(),f.getfPrice(),
					f.getfCategory(),f.getfDesc(),f.getfPic(),f.getfOnSale());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
   	 return rows;
   	 
    }
    //根据花名删除鲜花
    public int deleteflowersByFName(String name) {
   	 int rows=0;
   	 String sql="delete from flowers where f_name = ?";
   	 QueryRunner runner=new QueryRunner(JDBCUtil.getDataSource());
   	 try {
			rows=runner.update(sql,name);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
   	 return rows;
    }
    //修改鲜花
    public int updateFlowersById(Flowers f) {
   	 int rows=0;
   	 String sql="update flowers set f_name=?,f_price=?,f_category=?,f_desc=?,f_pic=?, "
   	 		+ "f_on_sale=? where f_id=?";
   	 QueryRunner runner=new QueryRunner(JDBCUtil.getDataSource());
   	 try {
			rows=runner.update(sql, f.getfName(),f.getfPrice(),
					f.getfCategory(),f.getfDesc(),f.getfPic(),f.getfOnSale()
					);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
   	 return rows;
   	 
    }
    
    //查询所有鲜花
    public List<Flowers> selectAll(){
		List<Flowers> flowers = new ArrayList<>();
		String sql="select f_name as fname,f_price as fprice,f_category as fcategory,"
				+ "f_desc as fdesc,f_pic as fpic,f_on_sale as fonsale from flowers";
		QueryRunner runner=new QueryRunner(JDBCUtil.getDataSource());
		try {
			flowers=runner.query(sql, new BeanListHandler<>(Flowers.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
       return flowers;
    }
  //根据花名查询鲜花
    public Flowers selectFlowersByName(String fName){
  		Flowers flowers = null;
  		String sql="select f_id as fid,f_name as fname,f_price as fprice,f_category as fcategory, "
  				+ "f_desc as fdesc,f_pic as fpic,f_on_sale as fonsale from flowers where f_name = ? ";
  		QueryRunner runner=new QueryRunner(JDBCUtil.getDataSource());
  		try {
  			flowers = runner.query(sql, new BeanHandler<>(Flowers.class),fName);
  		} catch (SQLException e) {
  			// TODO Auto-generated catch block
  			e.printStackTrace();
  		}
         return flowers;
      }

}
